﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Worktool.CODE_Chris.SQL_DATA;

namespace Worktool.CODE_Chris.DataSourceHelpers
{
    public class StammdatenHelper
    {
        private Stammdaten parent;
        private GeneralHelper helper;
        private SQL sql;

        private DataGridView gridView1;
        private SqlDataAdapter sqlDataAdapter1 = new SqlDataAdapter();
        private BindingSource bindingSource1 = new BindingSource();
        private DataTable dataTable1;

        private String select1;
        private String update1;

        private Boolean isPaste = false;
        private int countPasteError = 0;

        public StammdatenHelper(DataGridView gridView1, Stammdaten parent)
        {
            this.parent = parent;
            this.gridView1 = gridView1;
            init();
        }

        private void init()
        {
            sql = new SQL();
            helper = new GeneralHelper();

            populateSQLStrings();
        }

        #region Methoden für gridView1 (Hauptmaske)
        public void getData1()
        {
            try
            {
                gridView1.DataSource = bindingSource1;

                sqlDataAdapter1 = new SqlDataAdapter(select1, Login.connection);
                sqlDataAdapter1.UpdateCommand = new SqlCommand(update1, new SqlConnection(Login.connection));

                //Neu zu schreibende Parameter
                sqlDataAdapter1.UpdateCommand.Parameters.Add("@hasDatasheet", SqlDbType.Bit, 1, "hasDatasheet");
                sqlDataAdapter1.UpdateCommand.Parameters.Add("@hasManual", SqlDbType.Bit, 1, "hasManual");
                sqlDataAdapter1.UpdateCommand.Parameters.Add("@hasDmxAdress", SqlDbType.Bit, 1, "hasDmxAdress");
                sqlDataAdapter1.UpdateCommand.Parameters.Add("@isRack", SqlDbType.Bit, 1, "isRack");
                sqlDataAdapter1.UpdateCommand.Parameters.Add("@isRackarticle", SqlDbType.Bit, 1, "isRackarticle");
                sqlDataAdapter1.UpdateCommand.Parameters.Add("@isIpDevice", SqlDbType.Bit, 1, "isIpDevice");
                sqlDataAdapter1.UpdateCommand.Parameters.Add("@isBoxed", SqlDbType.Bit, 1, "isBoxed");
                sqlDataAdapter1.UpdateCommand.Parameters.Add("@isBulk", SqlDbType.Bit, 1, "isBulk");
                sqlDataAdapter1.UpdateCommand.Parameters.Add("@hasFoundation", SqlDbType.Bit, 1, "hasFoundation");
                sqlDataAdapter1.UpdateCommand.Parameters.Add("@updateDate", SqlDbType.Date, 10, "updateDate");
                sqlDataAdapter1.UpdateCommand.Parameters.Add("@updateUser", SqlDbType.NVarChar, 255, "updateUser");

                //Daten aus der Originalquelle
                SqlParameter parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@Original_ID", SqlDbType.Int, 50, "ID");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@Original_hasDatasheet", SqlDbType.Bit, 1, "hasDatasheet");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@Original_hasManual", SqlDbType.Bit, 1, "hasManual");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@Original_hasDmxAdress", SqlDbType.Bit, 1, "hasDmxAdress");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@Original_isRack", SqlDbType.Bit, 1, "isRack");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@Original_isRackarticle", SqlDbType.Bit, 1, "isRackarticle");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@Original_isIpDevice", SqlDbType.Bit, 1, "isIpDevice");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@Original_isBoxed", SqlDbType.Bit, 1, "isBoxed");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@Original_isBulk", SqlDbType.Bit, 1, "isBulk");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@Original_hasFoundation", SqlDbType.Bit, 1, "hasFoundation");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@Original_updateDate", SqlDbType.Date, 10, "updateDate");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@Original_updateUser", SqlDbType.NVarChar, 255, "updateUser");
                parameter.SourceVersion = DataRowVersion.Original;

                //Check auf Null-Werte
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@IsNull_hasDatasheet", SqlDbType.Int, 50, "hasDatasheet");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter.SourceColumnNullMapping = true;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@IsNull_hasManual", SqlDbType.Int, 10, "hasManual");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter.SourceColumnNullMapping = true;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@IsNull_hasDmxAdress", SqlDbType.Int, 50, "hasDmxAdress");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter.SourceColumnNullMapping = true;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@IsNull_isRack", SqlDbType.Int, 10, "isRack");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter.SourceColumnNullMapping = true;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@IsNull_isRackarticle", SqlDbType.Int, 50, "isRackarticle");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter.SourceColumnNullMapping = true;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@IsNull_isIpDevice", SqlDbType.Int, 10, "isIpDevice");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter.SourceColumnNullMapping = true;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@IsNull_isBoxed", SqlDbType.Int, 50, "isBoxed");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter.SourceColumnNullMapping = true;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@IsNull_isBulk", SqlDbType.Int, 10, "isBulk");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter.SourceColumnNullMapping = true;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@IsNull_hasFoundation", SqlDbType.Int, 50, "hasFoundation");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter.SourceColumnNullMapping = true;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@IsNull_updateDate", SqlDbType.Int, 10, "updateDate");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter.SourceColumnNullMapping = true;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@IsNull_updateUser", SqlDbType.Int, 50, "updateUser");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter.SourceColumnNullMapping = true;

                SqlCommandBuilder commandBuilder = new SqlCommandBuilder(sqlDataAdapter1);

                // Populate a new data table and bind it to the BindingSource.
                dataTable1 = new DataTable();
                dataTable1.Locale = System.Globalization.CultureInfo.InvariantCulture;
                sqlDataAdapter1.Fill(dataTable1);
                bindingSource1.DataSource = dataTable1;

                gridView1.AutoGenerateColumns = false;

                //Zellennachbearbeitung
                setHiddenColumns1();
                setDisabledColumns1();

                //Eventhandler setzen
                gridView1.CellFormatting -= new DataGridViewCellFormattingEventHandler(this.gridView1_CellFormatting);
                gridView1.CellEndEdit -= new DataGridViewCellEventHandler(this.gridView1_CellEndEdit);
                gridView1.CellValueChanged -= new DataGridViewCellEventHandler(this.gridView1_CellValueChanged);

                gridView1.CellFormatting += new DataGridViewCellFormattingEventHandler(this.gridView1_CellFormatting);
                gridView1.CellEndEdit += new DataGridViewCellEventHandler(this.gridView1_CellEndEdit);
                gridView1.CellValueChanged += new DataGridViewCellEventHandler(this.gridView1_CellValueChanged);
            }
            catch (Exception e)
            {
                sql.log("FEHLER", e, this.ToString() + ":" + System.Reflection.MethodBase.GetCurrentMethod().Name);
            }
        }

        private void gridView1_CellValueChanged(object sender, DataGridViewCellEventArgs e)
        {
            gridView1.CurrentRow.Cells["updateDate"].Value = DateTime.Today;
            gridView1.CurrentRow.Cells["updateUser"].Value = Login.user;
        }

        private void gridView1_CellEndEdit(object sender, DataGridViewCellEventArgs e)
        {
            //Hier werden die Daten der editierten Zelle wieder in die Datenbank geschrieben
            gridView1.BindingContext[gridView1.DataSource].EndCurrentEdit();
            updateGridView1();
        }

        public Boolean updateGridView1()
        {
            try
            {
                sqlDataAdapter1.Update(dataTable1);
                return true;
            }
            catch (DBConcurrencyException e)
            {
                if (isPaste)
                {
                    countPasteError++;
                }
                else
                {
                    if (MessageBox.Show("Der Datensatz wurde in der Zwischenzeit auf dem Server bearbeitet. Die Datensätze werden neu geladen.", "Fehler", MessageBoxButtons.OK) == DialogResult.OK)
                    {
                        parent.reloadAllData();
                    }
                }

                sql.log("FEHLER", e, this.ToString() + ":" + System.Reflection.MethodBase.GetCurrentMethod().Name);
                return false;
            }
        }

        private void gridView1_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
        {
            if (e.ColumnIndex != -1)
            {
                DataGridViewColumn col = gridView1.Columns[e.ColumnIndex];

                //Blaue Zellen
                if (col.Name == "ID" && col.DefaultCellStyle.BackColor != helper.getColors()[0]) col.DefaultCellStyle.BackColor = helper.getColors()[0];
                if (col.Name == "Manufacturer" && col.DefaultCellStyle.BackColor != helper.getColors()[0]) col.DefaultCellStyle.BackColor = helper.getColors()[0];
                if (col.Name == "Model" && col.DefaultCellStyle.BackColor != helper.getColors()[0]) col.DefaultCellStyle.BackColor = helper.getColors()[0];
                if (col.Name == "Description" && col.DefaultCellStyle.BackColor != helper.getColors()[0]) col.DefaultCellStyle.BackColor = helper.getColors()[0];
                if (col.Name == "Productnumber ASC" && col.DefaultCellStyle.BackColor != helper.getColors()[0]) col.DefaultCellStyle.BackColor = helper.getColors()[0];
                if (col.Name == "Weight (kg)" && col.DefaultCellStyle.BackColor != helper.getColors()[0]) col.DefaultCellStyle.BackColor = helper.getColors()[0];
                if (col.Name == "Power (W)" && col.DefaultCellStyle.BackColor != helper.getColors()[0]) col.DefaultCellStyle.BackColor = helper.getColors()[0];
                if (col.Name == "High (mm)" && col.DefaultCellStyle.BackColor != helper.getColors()[0]) col.DefaultCellStyle.BackColor = helper.getColors()[0];
                if (col.Name == "Width (mm)" && col.DefaultCellStyle.BackColor != helper.getColors()[0]) col.DefaultCellStyle.BackColor = helper.getColors()[0];
                if (col.Name == "Depth (mm)" && col.DefaultCellStyle.BackColor != helper.getColors()[0]) col.DefaultCellStyle.BackColor = helper.getColors()[0];
                if (col.Name == "Rackunits (HE)" && col.DefaultCellStyle.BackColor != helper.getColors()[0]) col.DefaultCellStyle.BackColor = helper.getColors()[0];
                if (col.Name == "Heatwaste (W)" && col.DefaultCellStyle.BackColor != helper.getColors()[0]) col.DefaultCellStyle.BackColor = helper.getColors()[0];
                if (col.Name == "updateUser" && col.DefaultCellStyle.BackColor != helper.getColors()[0]) col.DefaultCellStyle.BackColor = helper.getColors()[0];
                if (col.Name == "updateDate" && col.DefaultCellStyle.BackColor != helper.getColors()[0]) col.DefaultCellStyle.BackColor = helper.getColors()[0];
            }
        }

        private void setDisabledColumns1()
        {
            try
            {
                gridView1.Columns["ID"].ReadOnly = true;
                gridView1.Columns["Manufacturer"].ReadOnly = true;
                gridView1.Columns["Model"].ReadOnly = true;
                gridView1.Columns["Description"].ReadOnly = true;
                gridView1.Columns["Productnumber ASC"].ReadOnly = true;
                gridView1.Columns["Weight (kg)"].ReadOnly = true;
                gridView1.Columns["Power (W)"].ReadOnly = true;
                gridView1.Columns["High (mm)"].ReadOnly = true;
                gridView1.Columns["Width (mm)"].ReadOnly = true;
                gridView1.Columns["Depth (mm)"].ReadOnly = true;
                gridView1.Columns["Rackunits (HE)"].ReadOnly = true;
                gridView1.Columns["Heatwaste (W)"].ReadOnly = true;
                gridView1.Columns["updateUser"].ReadOnly = true;
                gridView1.Columns["updateDate"].ReadOnly = true;
            }
            catch (Exception e)
            {

            }
        }

        private void setHiddenColumns1()
        {
            try
            {
                gridView1.Columns["EK"].Visible = false;
                gridView1.Columns["VK"].Visible = false;
            }
            catch (Exception e)
            {

            }
        }

        public DataTable getTable1()
        {
            return dataTable1;
        }
        #endregion

        #region SQL-Befehle als Strings
        private void populateSQLStrings()
        {
            select1 = "SELECT Manufacturer, Model, Description, hasDatasheet, hasManual, hasDmxAdress, hasFoundation, isRack, isRackarticle, isIpDevice, isBoxed, isBulk, [Productnumber ASC], [Weight (kg)], [Power (W)], [High (mm)], [Width (mm)], [Depth (mm)], [Rackunits (HE)], [Heatwaste (W)], EK, VK, ID, updateUser, updateDate  FROM vw_std_engineering ORDER BY Manufacturer";
            update1 = "UPDATE vw_std_engineering SET hasDatasheet = @hasDatasheet, hasManual = @hasManual, hasDmxAdress = @hasDmxAdress, isRack = @isRack, isRackarticle = @isRackarticle, isIpDevice = @isIpDevice, isBoxed = @isBoxed, isBulk = @isBulk, hasFoundation = @hasFoundation, updateDate = @updateDate, updateUser = @updateUser "
                        +"WHERE        (ID = @Original_ID) "
                        +"AND (@IsNull_hasDatasheet = 1 AND hasDatasheet IS NULL OR hasDatasheet = @Original_hasDatasheet) "
                        +"AND (@IsNull_hasManual = 1 AND hasManual IS NULL OR hasManual = @Original_hasManual) "
                        +"AND (@IsNull_hasDmxAdress = 1 AND hasDmxAdress IS NULL OR hasDmxAdress = @Original_hasDmxAdress) "
                        +"AND (@IsNull_isRack = 1 AND isRack IS NULL OR isRack = @Original_isRack) "
                        +"AND (@IsNull_isRackarticle = 1 AND isRackarticle IS NULL OR isRackarticle = @Original_isRackarticle) "
                        +"AND (@IsNull_isIpDevice = 1 AND isIpDevice IS NULL OR isIpDevice = @Original_isIpDevice) "
                        +"AND (@IsNull_isBoxed = 1 AND isBoxed IS NULL OR isBoxed = @Original_isBoxed) "
                        +"AND (@IsNull_isBulk = 1 AND isBulk IS NULL OR isBulk = @Original_isBulk) "
                        +"AND (@IsNull_hasFoundation = 1 AND hasFoundation IS NULL OR hasFoundation = @Original_hasFoundation) "
                        + "AND (@IsNull_updateDate = 1 AND updateDate IS NULL OR updateDate = @Original_updateDate) "
                        + "AND (@IsNull_updateUser = 1 AND updateUser IS NULL OR updateUser = @Original_updateUser) ";
        }
        #endregion
    }
}
